package com.bloom.product.qms.event;

import cn.hutool.core.lang.UUID;
import com.alibaba.fastjson.JSON;
import com.bloom.cloud.core.db.DynamicDataSourceHolder;
import com.bloom.cloud.core.utils.DateUtils;
import com.bloom.cloud.processor.common.annotation.Executor;
import com.bloom.cloud.processor.common.annotation.ServiceExecutor;
import com.bloom.cloud.processor.common.model.TableColumnProps;
import com.bloom.cloud.processor.common.model.TsBusinessDefinition;
import com.bloom.cloud.processor.common.params.ServiceDefinitionParam;
import com.bloom.cloud.processor.common.params.ServiceInParam;
import com.bloom.cloud.processor.common.params.ServiceOutParam;
import com.bloom.cloud.processor.common.service.DynamicBaseExecutor;
import com.bloom.cloud.processor.expression.function.AviatorPropFunction;
import okhttp3.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.Instant;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * 导入已经关闭的事件和问题
 */
@ServiceExecutor(code = "QMS_DYNAMIC_PROBLEM_IMPORT")
public class QMSDynamicProblemImportExecutor extends DynamicBaseExecutor {
    @Executor
    public ServiceOutParam execute(ServiceDefinitionParam param) {
        String system = param.getInParam().getInstance();
        ServiceInParam inParam = param.getInParam();
        Assert.notNull(inParam.get("file"), "参数file不能为空.");

        String file = String.valueOf(inParam.get("file"));
        byte[] fileBytes = Base64.getDecoder().decode(file);

        String instance = "qms";
        final String TB_QUALITY_EVENT = "TB_QUALITY_EVENT";
        TsBusinessDefinition definitionQualityEvent = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TB_QUALITY_EVENT, instance), TsBusinessDefinition.class);
        TableColumnProps columnPropsQualityEvent = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TB_QUALITY_EVENT, instance), TableColumnProps.class);
        final String TB_QUALITY_PROBLEM = "TB_QUALITY_PROBLEM";
        TsBusinessDefinition definitionQualityProblem = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TB_QUALITY_PROBLEM, instance), TsBusinessDefinition.class);
        TableColumnProps columnPropsQualityProblem = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TB_QUALITY_PROBLEM, instance), TableColumnProps.class);
        //问题关联事件
        final String TB_PROBLEM_EVENT = "TB_PROBLEM_EVENT";
        TsBusinessDefinition definitionProblemEvent = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TB_PROBLEM_EVENT, instance), TsBusinessDefinition.class);
        TableColumnProps columnPropsProblemEvent = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TB_PROBLEM_EVENT, instance), TableColumnProps.class);
        //问题关联流程
        final String TB_PROBLEM_PROCESS = "TB_PROBLEM_PROCESS";
        TsBusinessDefinition definitionProblemProcess = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TB_PROBLEM_PROCESS, instance), TsBusinessDefinition.class);
        TableColumnProps columnPropsProblemProcess = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TB_PROBLEM_PROCESS, instance), TableColumnProps.class);
        //建立小组
        final String TB_MINOR_CHARGE_PERSON = "TB_MINOR_CHARGE_PERSON";
        TsBusinessDefinition definitionMinorChargePerson = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TB_MINOR_CHARGE_PERSON, instance), TsBusinessDefinition.class);
        TableColumnProps columnPropsMinorChargePerson = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TB_MINOR_CHARGE_PERSON, instance), TableColumnProps.class);
        //临时措施
        final String TB_INTERIM_MEASURE = "TB_INTERIM_MEASURE";
        TsBusinessDefinition definitionInterimMeasure = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TB_INTERIM_MEASURE, instance), TsBusinessDefinition.class);
        TableColumnProps columnPropsInterimMeasure = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TB_INTERIM_MEASURE, instance), TableColumnProps.class);
        //长期措施
        final String TB_EMERGENCY_MEASURE = "TB_EMERGENCY_MEASURE";
        TsBusinessDefinition definitionEmergencyMeasure = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TB_EMERGENCY_MEASURE, instance), TsBusinessDefinition.class);
        TableColumnProps columnPropsEmergencyMeasure = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TB_EMERGENCY_MEASURE, instance), TableColumnProps.class);

        HashMap<String, Object> rtnMap = new HashMap<>();
        try {
            InputStream inputStream = new ByteArrayInputStream(fileBytes);
            XSSFWorkbook workBook = getWorkbook(inputStream, "a.xlsx");
            if (null == workBook){
                return ServiceOutParam.failed("文件异常,请检查后重新上传Excel文件");
            }
            XSSFSheet sheet = workBook.getSheetAt(0);
            if (sheet ==null){
                return ServiceOutParam.failed("上传的Excel文件为空,请重新上传!!");
            }
            int maxRow = sheet.getLastRowNum();
            if (maxRow < 1){
                return ServiceOutParam.failed("未检测到Excel表格中有数据！");
            }

            Map<String,Map<String, Object>> companyMap = getCompanyInfo(system);
            Map<String,Map<String, Object>> plantMap = getPlantInfo(system);
            Map<String,Map<String, Object>> mainPlantMap = getMainPlantInfo(system);
            Map<String,Map<String, Object>> productMap = getProductInfo(system);
            Map<String,Map<String, Object>> departmentMap = getOrganizationInfo(system);
            Map<String,Map<String, Object>> userMap = getUserInfo(system);

            int judgeSize = 10;// 数据表格的列数（字段数）
            String value = null;
            Row row = null;
            int totalInt = 0;

            Map<Integer,Map<String,Object>> eventAddMap = new HashMap<>();
            Map<Integer,Map<String,Object>> problemAddMap = new HashMap<>();
            Map<Integer,Map<String,Object>> interimMeasureAddMap = new HashMap<>();
            Map<Integer,Map<String,Object>> emergencyMeasureAddMap = new HashMap<>();
            //原因分析制造
            Map<Integer,String> reasonAnalysisAddMap = new HashMap<>();

            String dateTime = DateUtils.formatDate(new Date(),"yyyyMMdd");
            final List<String> errorInfos = new ArrayList<String>();
            for (int i = 1; i <= maxRow; i++){
                Map<String,Object> eventMap = new HashMap<>();
                Map<String,Object> problemMap = new HashMap<>();
                Map<String,Object> interimMeasureMap = new HashMap<>();
                Map<String,Object> emergencyMeasureMap = new HashMap<>();

                row = sheet.getRow(i); // 获得行
                int index = 0;// 第一列
                //第一列的值
                value = getCell(row, index);
                if (StringUtils.isBlank(value)) {
                    // 整行为空时，导入操作中断
                    if (isAllLineBlank(row, judgeSize)) {
                        break;
                    }
                }
                //这个是为了计算导入成功总行数   放这别动就行
                totalInt++;
                //公司
                if (StringUtils.isBlank(value)) {
                    errorInfos.add("第" + (i + 1) + "行导入失败，错误信息->[公司必填]<br/>");
                    continue;
                }
                if (!companyMap.containsKey(value)) {
                    errorInfos.add("第" + (i + 1) + "行导入失败，错误信息->[公司不存在]<br/>");
                    continue;
                }
                Map<String,Object> companyMapValue = companyMap.get(value);
                eventMap.put("company_id",companyMapValue.get("id"));
                eventMap.put("company_code",companyMapValue.get("company_code"));
                eventMap.put("company_name",companyMapValue.get("company_name"));
                problemMap.put("company_id",companyMapValue.get("id"));
                problemMap.put("company_code",companyMapValue.get("company_code"));
                problemMap.put("company_name",companyMapValue.get("company_name"));

                //第2列的值   工厂
                index++;
                value = getCell(row, index);
                if (StringUtils.isBlank(value)) {
                    errorInfos.add("第" + (i + 1) + "行导入失败，错误信息->[工厂必填]<br/>");
                    continue;
                }
                if (!plantMap.containsKey(value)) {
                    errorInfos.add("第" + (i + 1) + "行导入失败，错误信息->[工厂不存在]<br/>");
                    continue;
                }
                Map<String,Object> plantMapValue = plantMap.get(value);
                eventMap.put("plant_id",plantMapValue.get("id"));
                eventMap.put("plant_code",plantMapValue.get("plant_code"));
                eventMap.put("plant_name",plantMapValue.get("plant_name"));
                problemMap.put("plant_id",plantMapValue.get("id"));
                problemMap.put("plant_code",plantMapValue.get("plant_code"));
                problemMap.put("plant_name",plantMapValue.get("plant_name"));

                //第3列的值   产品
                index++;
                value = getCell(row, index);
                if (StringUtils.isBlank(value)) {
                    errorInfos.add("第" + (i + 1) + "行导入失败，错误信息->[产品总成必填]<br/>");
                    continue;
                }
                if (!productMap.containsKey(value)) {
                    errorInfos.add("第" + (i + 1) + "行导入失败，错误信息->[产品总成不存在]<br/>");
                    continue;
                }
                Map<String,Object> productMapValue = productMap.get(value);
                eventMap.put("product_id",productMapValue.get("id"));
                eventMap.put("assembly_code",productMapValue.get("assembly_code"));
                eventMap.put("product_name",productMapValue.get("name"));
                problemMap.put("product_code",productMapValue.get("assembly_code"));
                problemMap.put("product_name",productMapValue.get("name"));








                eventAddMap.put(i,eventMap);
                problemAddMap.put(i,problemMap);
            }

            if(errorInfos.size() <= 0){
                String eventType = "PROJECT";
                String eventStatus = "UPGRADE_QUALITY_PROBLEM";
                String problemType = "GENERAL_COMPLAINT_PROBLEM";
                String problemStatus = "CLOSED";
                String processType = "GENERAL_PROBLEM_FLOW";
                String processStatus = "CLOSE";
                for (int i = 1; i <= maxRow; i++){
                    String processInstanceId = UUID.randomUUID().toString();
                    Map<String,Object> eventMap = eventAddMap.get(i);
                    String eventCode = AviatorPropFunction.businessCode("ZLSJ"+dateTime, 4L);
                    eventMap.put("event_code",eventCode);
                    eventMap.put("event_type",eventType);
                    eventMap.put("event_status",eventStatus);
                    Integer eventId = dynamicDataExecutor.save(eventMap,definitionQualityEvent,columnPropsQualityEvent);
                    Map<String,Object> problemMap = problemAddMap.get(i);
                    String problemCode = AviatorPropFunction.businessCode("ZLWT"+dateTime, 4L);
                    problemMap.put("event_code",eventCode);
                    problemMap.put("problem_code",problemCode);
                    problemMap.put("event_id",eventId);
                    problemMap.put("event_type",eventType);
                    problemMap.put("problem_status",problemStatus);
                    problemMap.put("quality_problem_type",problemType);
                    problemMap.put("process_type",processType);
                    problemMap.put("process_status",processStatus);
                    problemMap.put("if_hidden","N");
                    problemMap.put("current_progress","4/4");
                    problemMap.put("process_instance_id",processInstanceId);
                    Integer problemId = dynamicDataExecutor.save(problemMap,definitionQualityProblem,columnPropsQualityProblem);
                    Map<String,Object> problemEventMap = new HashMap<>();
                    problemEventMap.put("problem_id",problemId);
                    problemEventMap.put("event_id",eventId);
                    dynamicDataExecutor.save(problemEventMap,definitionProblemEvent,columnPropsProblemEvent);
                    Map<String,Object> processMap = new HashMap<>();
                    processMap.put("problem_id",problemId);
                    processMap.put("problem_code",problemCode);
                    processMap.put("problem_type",problemType);
                    processMap.put("process_type",processType);
                    processMap.put("process_status",processStatus);
                    processMap.put("is_lesson_learn","Y");
                    processMap.put("auto_lesson_learn","Y");
                    processMap.put("process_instance_id",processInstanceId);
                    //原因分析
                    processMap.put("make_root_cause",reasonAnalysisAddMap.get(i));
                    processMap.put("if_escape_point","N");
                    Integer problemProcessId = dynamicDataExecutor.save(processMap,definitionProblemProcess,columnPropsProblemProcess);

                    Map<String,Object> interimMeasureMap = interimMeasureAddMap.get(i);
                    interimMeasureMap.put("problem_process_id",problemProcessId);
                    dynamicDataExecutor.save(interimMeasureMap,definitionInterimMeasure,columnPropsInterimMeasure);
                    Map<String,Object> emergencyMeasureMap = emergencyMeasureAddMap.get(i);
                    emergencyMeasureMap.put("problem_process_id",problemProcessId);
                    dynamicDataExecutor.save(emergencyMeasureMap,definitionEmergencyMeasure,columnPropsEmergencyMeasure);

                }
            }

            rtnMap.put("totalNum",totalInt);
            if(errorInfos.size() > 0){
                String msg = "";
                for(String errorInfo:errorInfos){
                    msg += errorInfo;
                }
                rtnMap.put("errorInfos",msg);
                rtnMap.put("hasError",true);
                rtnMap.put("errorNum",errorInfos.size());
            }else{
                rtnMap.put("hasError",false);
            }
            rtnMap.put("successNum",totalInt-errorInfos.size());

        }catch (Exception e){
            e.printStackTrace();
            return ServiceOutParam.failed(e.getMessage());
        }
        HashMap<String, Object> params = new HashMap<>();
        params.put("returnData",rtnMap);
        return ServiceOutParam.success(params);
    }

    private Date calDate(String date){
        if(date.contains("/")){
            String[] year_month_day = date.split("/");
            String year = year_month_day[0];
            if(year.length() == 2){
                year = "20"+year;
            }
            date = year+"-"+String.format("%02d", Integer.parseInt(year_month_day[1]))+"-"+String.format("%02d", Integer.parseInt(year_month_day[2]));
        }else if(date.contains("-")){
            String[] year_month_day = date.split("-");
            String year = year_month_day[0];
            if(year.length() == 2){
                year = "20"+year;
            }
            date = year+"-"+String.format("%02d", Integer.parseInt(year_month_day[1]))+"-"+String.format("%02d", Integer.parseInt(year_month_day[2]));
        }else if(date.contains(".")){
            String[] year_month_day = date.split(".");
            String year = year_month_day[0];
            if(year.length() == 2){
                year = "20"+year;
            }
            date = year+"-"+String.format("%02d", Integer.parseInt(year_month_day[1]))+"-"+String.format("%02d", Integer.parseInt(year_month_day[2]));
        }
        return DateUtils.parse(date,DateUtils.FORMAT_DATE_DEFAULT);
    }

    private Map<String,Map<String, Object>> getCompanyInfo(String system){
        DynamicDataSourceHolder.setDbType(system);
        StringBuffer sql = new StringBuffer();
        sql.append("  select id,company_code,company_name from tm_company where mark_for_delete = 0 ");
        List<Map<String, Object>> companyInfoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        Map<String,Map<String, Object>> rtnMap = new HashMap<>();
        if(companyInfoList != null && companyInfoList.size() > 0){
            for(Map<String, Object> map : companyInfoList){
                rtnMap.put(String.valueOf(map.get("company_code")),map);
            }
        }
        return rtnMap;
    }

    private Map<String,Map<String, Object>> getPlantInfo(String system){
        DynamicDataSourceHolder.setDbType(system);
        StringBuffer sql = new StringBuffer();
        sql.append("  select id,plant_code,plant_name from tm_plant where mark_for_delete = 0 ");
        List<Map<String, Object>> companyInfoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        Map<String,Map<String, Object>> rtnMap = new HashMap<>();
        if(companyInfoList != null && companyInfoList.size() > 0){
            for(Map<String, Object> map : companyInfoList){
                rtnMap.put(String.valueOf(map.get("plant_code")),map);
            }
        }
        return rtnMap;
    }

    /**
     * 总成产品
     * @param system
     * @return
     */
    private Map<String,Map<String, Object>> getProductInfo(String system){
        DynamicDataSourceHolder.setDbType(system);
        StringBuffer sql = new StringBuffer();
        sql.append("  select id,name,assembly_code from tb_standard_assembly_product where mark_for_delete = 0 ");
        List<Map<String, Object>> companyInfoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        Map<String,Map<String, Object>> rtnMap = new HashMap<>();
        if(companyInfoList != null && companyInfoList.size() > 0){
            for(Map<String, Object> map : companyInfoList){
                rtnMap.put(String.valueOf(map.get("name")),map);
            }
        }
        return rtnMap;
    }

    private Map<String,Map<String, Object>> getMainPlantInfo(String system){
        DynamicDataSourceHolder.setDbType(system);
        StringBuffer sql = new StringBuffer();
        sql.append("  select id,main_engine_plant_code,main_engine_plant_name from tm_main_engine_plant where mark_for_delete = 0 ");
        List<Map<String, Object>> companyInfoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        Map<String,Map<String, Object>> rtnMap = new HashMap<>();
        if(companyInfoList != null && companyInfoList.size() > 0){
            for(Map<String, Object> map : companyInfoList){
                rtnMap.put(String.valueOf(map.get("main_engine_plant_name")),map);
            }
        }
        return rtnMap;
    }

    private Map<String,Map<String, Object>> getOrganizationInfo(String system){
        DynamicDataSourceHolder.setDbType(system+"_admin");
        StringBuffer sql = new StringBuffer();
        sql.append("  select id,organization_name,organization_code from ts_organization where mark_for_delete = 0 ");
        List<Map<String, Object>> organizationInfoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        Map<String,Map<String, Object>> rtnMap = new HashMap<>();
        if(organizationInfoList != null && organizationInfoList.size() > 0){
            for(Map<String, Object> map : organizationInfoList){
                rtnMap.put(String.valueOf(map.get("organization_name")),map);
            }
        }
        return rtnMap;
    }

    private Map<String,Map<String, Object>> getUserInfo(String system){
        DynamicDataSourceHolder.setDbType(system+"_admin");
        StringBuffer sql = new StringBuffer();
        sql.append("  select u.id,u.account,u.user_name,o.id as organization_id,o.organization_code,o.organization_name from ts_user u left join ts_organization o on u.organization_id = o.id where u.mark_for_delete = 0 and o.mark_for_delete = 0 ");
        List<Map<String, Object>> positionInfoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        Map<String,Map<String, Object>> rtnMap = new HashMap<>();
        if(positionInfoList != null && positionInfoList.size() > 0){
            for(Map<String, Object> map : positionInfoList){
                rtnMap.put(String.valueOf(map.get("user_name")),map);
            }
        }
        return rtnMap;
    }



    public XSSFWorkbook getWorkbook(InputStream ins, String fileName) throws Exception{
        XSSFWorkbook wk = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            wk = new XSSFWorkbook(ins);
        } else if (".xlsx".equals(fileType)) {
            wk = new XSSFWorkbook(ins);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return wk;
    }

    public static String getCell(Row row, int colIndex) {
        if (row != null) {
            Cell cell = row.getCell(colIndex);
            if (cell != null) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    return cell.getStringCellValue().trim();
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    return NumberToTextConverter.toText(cell.getNumericCellValue());
                } else {
                    return getInfo(cell);
                }
            }
        }
        return "";
    }

    public static String getInfo(Object obj) {
        if (obj != null) {
            return obj.toString().trim();
        }
        return "";
    }

    public static boolean isAllLineBlank(Row row, int judgeSize) {
        boolean boo = true;
        for (int i = 0; i < judgeSize; i++) {
            if (StringUtils.isNotBlank(getCell(row, i))) {
                boo = false;
                break;
            }
        }
        return boo;
    }

}
